package com.sms.server;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Locale;

import jxl.CellView;
import jxl.SheetSettings;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormats;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.apache.log4j.Logger;

/**
 * @author home
 *
 */
public class JExcelExportDAO {
    
    private static final Logger LOG = Logger.getLogger(JExcelExportDAO.class);
    
    private WritableWorkbook wb;
    protected WritableSheet sheet;
    private int worksheetCount;
    /**
     * 
     */
    protected JExcelExportDAO() {
        
    }

    /**
     * @param exportFile
     * @throws IOException
     */
    public void createWorkbook(File exportFile) throws IOException {
    	
        LOG.info("Now attempting to create a new workbook, sheet and data for " + exportFile.getAbsolutePath());
        /*Create a new Workbook*/
        LOG.debug("Creating a new workbook...");
        wb = Workbook.createWorkbook(exportFile);
        sheet = wb.createSheet("Report", 0);
    }
    public void createWorkbook(ByteArrayOutputStream baos) throws IOException {
    	
    	WorkbookSettings wbSettings = new WorkbookSettings();

		wbSettings.setLocale(new Locale("en", "EN"));

        /*Create a new Workbook*/
        LOG.debug("Creating a new workbook...");
        wb = Workbook.createWorkbook(baos,wbSettings);
        sheet = wb.createSheet("Report", 0);
    }
    
    public void createWorkbook(File exportFile, File templateFile) throws IOException, BiffException {
        
        LOG.info("Now attempting to create a new workbook, sheet and data for " + exportFile.getAbsolutePath());
        /*Create a new Workbook*/
        LOG.debug("Creating a new workbook...");
        wb = Workbook.createWorkbook(exportFile, Workbook.getWorkbook(templateFile));
        worksheetCount = 0;
    }
   
    /**
     * 
     * @Description : This method is used to create the worksheet on the created workbook and the param is used to name the sheet 
     * @Called_Method :
     *
     * @param sheetName - to naming the created sheet.
     */
    public void createSheet(String sheetName) {
        LOG.debug("Creating a new sheet...");
        /*Create a new Sheet*/
        sheet = wb.createSheet(sheetName, worksheetCount++);       
    }
    
    public void openSheet(String sheetName) {
    	LOG.debug("Opening a new sheet... Name :"+sheetName);
        /*Opening a new Sheet*/
		sheet = wb.getSheet(sheetName);

	}

    /**
     * @return
     */
    protected SheetSettings getSheetSettings() {
        return sheet.getSettings();
    }

    /**
     * @throws IOException
     * @throws WriteException
     */
    public void closeWorkbook() throws IOException, WriteException {
		wb.write();
		wb.close();
    }
    
    /**
     * 
     * @param column
     * @param row
     * @param c
     * @throws WriteException
     */
    public void setCellColor(int column, int row, Colour c) throws WriteException {
        WritableCell cell = sheet.getWritableCell(column, row);
        WritableCellFormat cf = new WritableCellFormat();
        cf.setBackground(c);
        cell.setCellFormat(cf);
    }

    /**
     * @param column
     * @param c
     * @throws WriteException
     */
    public void setCustomSize(int column, int size) throws WriteException {
        CellView cv = sheet.getColumnView(column);
        cv.setSize(size);
        sheet.setColumnView(column, cv);
    }
    
    /**
     *
     * @param column
     * @param c
     * @throws WriteException
     */
    public void setAutoSize(int column) throws WriteException {
        CellView cv = sheet.getColumnView(column);
        cv.setAutosize(true);
        sheet.setColumnView(column, cv);
    }
    
    
    /**
     * @param x1 - 
     * @param y1
     * @param x2
     * @param y2
     * @param c
     * @throws WriteException
     */
    protected void setAreaColor(int x1, int y1, int x2, int y2, Colour c)
    													throws WriteException {
    	if (x1 < x2 && y1 < y2)
    		for (int x = x1; x < x2; x ++) 
    			for (int y = y1; y < y2; y ++) 
    				setCellColor(x, y, c);    			    		
    }
    
    /**
     * @param column
     * @param row
     * @param data
     * @throws WriteException
     */
	public void setCellValueWithWrap(int column, int row, Object data) throws WriteException {
		setCellValue(column, row, data, false, true, false, false);
	}
    
    /**
     * 
     * @param column
     * @param row
     * @param data
     * @throws WriteException
     */
	public void setCellValue(int column, int row, Object data) throws WriteException {
		setCellValue(column, row, data, false, false, false, false);
	}
    
	/**
     * @param column
     * @param row
     * @param data
     * @param isCurrency 
     * @param wrap 
     * @param isDatetime 
     * @throws WriteException
     */
	public void setCellPriceValue(int column, int row, Object data) throws WriteException {
		setCellValue(column, row, data, true, false, false, false);
    }
	
    /**
     * 
     * @param column
     * @param row
     * @param data
     * @param isCurrency 
     * @param wrap 
     * @param isDatetime 
     * @param isTime 
     * @throws WriteException
     */
	private void setCellValue(int column, int row, Object data, boolean isCurrency, boolean wrap, boolean isDatetime, boolean isTime) throws WriteException {
		WritableFont CELL_FONT = new WritableFont(WritableFont.createFont("Calibri"), 10, WritableFont.NO_BOLD);
    	WritableCellFormat cellFormat = null; 
    	if(isCurrency)
    		cellFormat = new WritableCellFormat(CELL_FONT, NumberFormats.ACCOUNTING_RED_FLOAT);
    	else
    		cellFormat = new WritableCellFormat(CELL_FONT);
    	
    	if(wrap) cellFormat.setWrap(wrap);
    	
    	cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);

    	if (data instanceof Integer) {
    		cellFormat.setAlignment(Alignment.RIGHT);
    		Number num = new Number(column, row, (Integer)data, cellFormat);
    		sheet.addCell(num);        
		}
    	else if (data instanceof Long) {
    		cellFormat.setAlignment(Alignment.RIGHT);
    		Number num = new Number(column, row, (Long)data, cellFormat);
    		sheet.addCell(num);        
		}
    	else if (data instanceof Float) {
    		cellFormat.setAlignment(Alignment.RIGHT);
    		Number num = new Number(column, row, (Float)data, cellFormat);
            sheet.addCell(num); 
    	}
    	else if (data instanceof Double) {
    		cellFormat.setAlignment(Alignment.RIGHT);
    		Number num = new Number(column, row, (Double)data, cellFormat);
            sheet.addCell(num); 
    	}
    	else if (data instanceof BigDecimal) {
    		cellFormat.setAlignment(Alignment.RIGHT);
    		Number num = new Number(column, row, ((BigDecimal)data).doubleValue(), cellFormat);
            sheet.addCell(num); 
    	}
    	else if (data instanceof Boolean) {
    		cellFormat.setAlignment(Alignment.RIGHT);
    		jxl.write.Boolean num = new jxl.write.Boolean(column, row, (Boolean)data, cellFormat);
    		sheet.addCell(num); 
    	}
    	else if (data instanceof Date) {
    		DateFormat sdfValue = null;
    		cellFormat.setAlignment(Alignment.RIGHT);
    		if(isTime)
    			sdfValue = new SimpleDateFormat("HH:mm:ss");
    		else if(isDatetime)
    			sdfValue = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
    		else
    			sdfValue = new SimpleDateFormat("MM/dd/yyyy");
    			
    		Label dt = new Label(column, row, sdfValue.format((Date)data), cellFormat);
            sheet.addCell(dt); 
    	}
    	else 
    	{	
    		cellFormat.setAlignment(Alignment.LEFT);
    		Label l = new Label(column, row, (String) data, cellFormat);
            sheet.addCell(l);
    	}
    }
	
	/**
	 * 
	 * @param column
	 * @param row
	 * @param data
	 * @param isTimeAlone
	 * @param isDatetime
	 * @throws WriteException
	 */
	public void setCellTimeValue(int column, int row, Object data) throws WriteException {
		setCellValue(column, row, data, false, false, false, true);
    }
	
	/**
	 * @param column
	 * @param row
	 * @param data
	 * @param isTimeAlone
	 * @param isDatetime
	 * @throws WriteException
	 */
	public void setCellDateTimeValue(int column, int row, Object data) throws WriteException {
		setCellValue(column, row, data, false, false, true, false);
    }
	
	/**
	 * @param column
	 * @param row
	 * @param s
	 * @throws WriteException
	 */
	public void setSubColumnHeader(int column, int row,  Object data , boolean isDatetime) throws WriteException {
		setSubColumnHeader(column, row, data, false, isDatetime);
    }
	  public void setSubColumnHeader(int column, int row, Object data, Boolean wrap, boolean isDatetime) throws WriteException {
		  setSubColumnHeader(column, row, data, wrap, 12, isDatetime);
	    }
	  
	  public void setSubColumnHeader(int column, int row,  Object data, Boolean wrap, int fontSize, boolean isDatetime) throws WriteException {
	    
	    	
			WritableFont COLUMN_HEADER_FONT = new WritableFont(WritableFont.createFont("Calibri"), fontSize, WritableFont.BOLD);
	        WritableCellFormat cellFormat = new WritableCellFormat(COLUMN_HEADER_FONT);
	       // cellFormat.setBackground(c);
	       // cellFormat.setBorder(Border.ALL, BorderLineStyle.NONE);
	        cellFormat.setWrap(wrap);
	    	//Label l = new Label(column, row,data, cellFormat);
	    	//sheet.addCell(l);
	    	if (data instanceof Date) {
	    		DateFormat sdfValue = null;
	    		cellFormat.setAlignment(Alignment.RIGHT);
	    		
	    		 if(isDatetime)
	    			sdfValue = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
	    		else
	    			sdfValue = new SimpleDateFormat("MM/dd/yyyy");
	    			
	    		Label dt = new Label(column, row, sdfValue.format((Date)data), cellFormat);
	            sheet.addCell(dt); 
	    	}
	    	else 
	    	{	
	    		cellFormat.setAlignment(Alignment.LEFT);
	    		Label l = new Label(column, row, (String) data, cellFormat);
	            sheet.addCell(l);
	    	}
	    }
	
	/**
	 * 
	 *
	 * @param column
	 * @param row
	 * @param s
	 * @throws WriteException
	 */
	public void setColumnHeader(int column, int row, String s) throws WriteException {
    	setColumnHeader(column, row, s, false);
    }
	
	public void setColumnHeader(int column, int row, String s, Alignment alignment) throws WriteException {
    	setColumnHeader(column, row, s, false,  Colour.GRAY_25, alignment);
    }
	
	
	
	/**
	 * 
	 * @param column
	 * @param row
	 * @param s
	 * @param c
	 * @throws WriteException
	 */
	public void setColumnHeader(int column, int row, String s, Colour c) throws WriteException {
    	setColumnHeader(column, row, s, false, c);
    }
	
	/**
	 * 
	 * @param column
	 * @param row
	 * @param s
	 * @param wrap
	 * @throws WriteException
	 */
    public void setColumnHeader(int column, int row, String s, Boolean wrap) throws WriteException {
    	setColumnHeader(column, row, s, wrap, Colour.GRAY_25);
    }
    
    
    /**
     * 
     * @param column
     * @param row
     * @param s
     * @param wrap
     * @param c
     * @throws WriteException
     */
    public void setColumnHeader(int column, int row, String s, Boolean wrap, Colour c) throws WriteException {
    	setColumnHeader(column, row, s, wrap, c, Alignment.CENTRE);
    }
    
    public void setColumnHeader(int column, int row, String s, Boolean wrap, Colour c, Alignment alignment) throws WriteException {
    	setColumnHeader(column, row, s, wrap, c, alignment, 10);
    }
    
    public void setColumnHeader(int column, int row, String s, Boolean wrap, Colour c, Alignment alignment, int fontSize) throws WriteException {
    	WritableFont COLUMN_HEADER_FONT = new WritableFont(WritableFont.createFont("Calibri"), fontSize, WritableFont.BOLD);
        WritableCellFormat cellFormat = new WritableCellFormat(COLUMN_HEADER_FONT);
        cellFormat.setBackground(c);
        cellFormat.setAlignment(alignment);
        cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
        cellFormat.setWrap(wrap);
        CellView autoSizeCellView = new CellView();
        autoSizeCellView.setAutosize(true);

    	Label l = new Label(column, row, s, cellFormat);
    	sheet.addCell(l);
    	sheet.setColumnView(column, autoSizeCellView);

    }
    
  
    
    
    /**
     * 
     * @param xColumn
     * @param xRow
     * @param yColumn
     * @param yRow
     * @param s
     * @throws WriteException
     */
    public void setHeader(int xColumn, int xRow, int yColumn, int yRow, String s) throws WriteException {
    	sheet.mergeCells(xColumn, xRow, yColumn, yRow);
    	WritableFont headerFont = new WritableFont(WritableFont.createFont("Calibri"), 15, WritableFont.BOLD);
        WritableCellFormat arial10BoldFormat = new WritableCellFormat(headerFont);
        arial10BoldFormat.setAlignment(Alignment.LEFT);
        arial10BoldFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
    	Label l = new Label(xColumn, xRow, s, arial10BoldFormat);
    	sheet.addCell(l);
    }
    
    public void setSubHeader(int xColumn, int xRow,  String s) throws WriteException {
    	WritableFont headerFont = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.BOLD);
        WritableCellFormat arial10BoldFormat = new WritableCellFormat(headerFont);
        arial10BoldFormat.setAlignment(Alignment.LEFT);
        arial10BoldFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
    	Label l = new Label(xColumn, xRow, s, arial10BoldFormat);
    	sheet.addCell(l);
    }
    
    /**
     * 
     * @param xColumn
     * @param xRow
     * @param yColumn
     * @param yRow
     * @param s
     * @param isHeader
     * @throws RowsExceededException
     * @throws WriteException
     */
    protected void mergeCells(int xColumn, int xRow, int yColumn, int yRow, String s, boolean isHeader) throws RowsExceededException, WriteException {
    	mergeCells(xColumn, xRow, yColumn, yRow, s, Colour.GRAY_25, isHeader);
    }
    
   /**
    * 
    * @param xColumn
    * @param xRow
    * @param yColumn
    * @param yRow
    * @throws RowsExceededException
    * @throws WriteException
    */
    protected void mergeCells(int xColumn, int xRow, int yColumn, int yRow) throws RowsExceededException, WriteException {
    	sheet.mergeCells(xColumn, xRow, yColumn, yRow);
    }
    
    /**
     * 
     * @param xColumn
     * @param xRow
     * @param yColumn
     * @param yRow
     * @param s
     * @param colour
     * @param isHeader
     * @throws RowsExceededException
     * @throws WriteException
     */
    protected void mergeCells(int xColumn, int xRow, int yColumn, int yRow, String s, Colour colour, boolean isHeader) throws RowsExceededException, WriteException {
    	WritableFont CELL_FONT = new WritableFont(WritableFont.createFont("Calibri"), 10, WritableFont.NO_BOLD);
    	WritableFont COLUMN_HEADER_FONT = new WritableFont(WritableFont.createFont("Calibri"), 10, WritableFont.BOLD);
    	sheet.mergeCells(xColumn, xRow, yColumn, yRow);
    	WritableCellFormat arial10BoldFormat = null;
    	if(isHeader)
    	{
        	arial10BoldFormat = new WritableCellFormat(COLUMN_HEADER_FONT);
	    	arial10BoldFormat.setBackground(colour);
	    	arial10BoldFormat.setAlignment(Alignment.CENTRE);
    	}
    	else
    	{
    		arial10BoldFormat = new WritableCellFormat(CELL_FONT);
    	}
    	arial10BoldFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
    	Label l = new Label(xColumn, xRow, s, arial10BoldFormat);
    	sheet.addCell(l);
    }
}
